//-----------------------------------------------------------------------
// <copyright file="ShippingStatusReportDAO.cs" company="Pyramid Consulting">
//     Copyright (c) Pyramid Consulting. All rights reserved.
// </copyright>
//-----------------------------------------------------------------------
	
using System;
using System.Data;
using MIMS.WRS.Entities;
using MyFramework;
using MyFramework.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace MIMS.WRS.Data
{	
	public class ShippingStatusReportDAO : ShippingStatusReportDAOBase
	{
		public ShippingStatusReportDAO()
		{
		}

		#region "override methods"
		public override void CreateShippingStatusReport(ShippingStatusReport shippingStatusReport)
		{
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportInsert");
                
				dbCommandWrapper.AddInParameter("@REQ", DbType.Int32, shippingStatusReport.REQ);
				dbCommandWrapper.AddInParameter("@OrderDate", DbType.DateTime, shippingStatusReport.OrderDate==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.OrderDate);
				dbCommandWrapper.AddInParameter("@ContainerSize", DbType.AnsiString, shippingStatusReport.ContainerSize);
				dbCommandWrapper.AddInParameter("@LP", DbType.AnsiStringFixedLength, shippingStatusReport.LP);
				dbCommandWrapper.AddInParameter("@PONo_Office", DbType.AnsiStringFixedLength, shippingStatusReport.PONo_Office);
				dbCommandWrapper.AddInParameter("@PONo_Type", DbType.AnsiStringFixedLength, shippingStatusReport.PONo_Type);
				dbCommandWrapper.AddInParameter("@PONo_Supplier", DbType.AnsiString, shippingStatusReport.PONo_Supplier);
				dbCommandWrapper.AddInParameter("@PONo_Number", DbType.Int32, shippingStatusReport.PONo_Number);
				dbCommandWrapper.AddInParameter("@IsPrimary", DbType.AnsiStringFixedLength, shippingStatusReport.IsPrimary);
				dbCommandWrapper.AddInParameter("@PONo_Consolidation", DbType.Int32, shippingStatusReport.PONo_Consolidation==Nulls.NullInteger?DBNull.Value:(object)shippingStatusReport.PONo_Consolidation);
				dbCommandWrapper.AddInParameter("@AccNo", DbType.AnsiString, shippingStatusReport.AccNo);
				dbCommandWrapper.AddInParameter("@CustomerOrLocation", DbType.String, shippingStatusReport.CustomerOrLocation);
				dbCommandWrapper.AddInParameter("@FinalDestination", DbType.String, shippingStatusReport.FinalDestination);
				dbCommandWrapper.AddInParameter("@Description", DbType.String, shippingStatusReport.Description);
				dbCommandWrapper.AddInParameter("@FactoryETD", DbType.DateTime, shippingStatusReport.FactoryETD==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.FactoryETD);
				dbCommandWrapper.AddInParameter("@ShippingDate", DbType.DateTime, shippingStatusReport.ShippingDate==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.ShippingDate);
				dbCommandWrapper.AddInParameter("@ShippingLine", DbType.AnsiString, shippingStatusReport.ShippingLine);
				dbCommandWrapper.AddInParameter("@Vessel", DbType.String, shippingStatusReport.Vessel);
				dbCommandWrapper.AddInParameter("@ContainerNo", DbType.AnsiString, shippingStatusReport.ContainerNo);
				dbCommandWrapper.AddInParameter("@ETA", DbType.DateTime, shippingStatusReport.ETA==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.ETA);
				dbCommandWrapper.AddInParameter("@Week", DbType.Int32, shippingStatusReport.Week);
				dbCommandWrapper.AddInParameter("@ArrivalPort", DbType.String, shippingStatusReport.ArrivalPort);
				dbCommandWrapper.AddInParameter("@PI", DbType.AnsiString, shippingStatusReport.PI);
				dbCommandWrapper.AddInParameter("@Payment", DbType.AnsiStringFixedLength, shippingStatusReport.Payment);
				dbCommandWrapper.AddInParameter("@DocReceived", DbType.Boolean, shippingStatusReport.DocReceived);
				dbCommandWrapper.AddInParameter("@CompletedDate", DbType.DateTime, shippingStatusReport.CompletedDate==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.CompletedDate);
				dbCommandWrapper.AddInParameter("@BulkType", DbType.AnsiString, shippingStatusReport.BulkType);
				dbCommandWrapper.AddInParameter("@ModifiedDate", DbType.DateTime, shippingStatusReport.ModifiedDate);
				dbCommandWrapper.AddInParameter("@BulkOrderNo", DbType.Int32, shippingStatusReport.BulkOrderNo==Nulls.NullInteger?DBNull.Value:(object)shippingStatusReport.BulkOrderNo);
				dbCommandWrapper.AddInParameter("@PayAuth", DbType.AnsiStringFixedLength, shippingStatusReport.PayAuth);
				dbCommandWrapper.AddInParameter("@ContainerDeliveried", DbType.DateTime, shippingStatusReport.ContainerDeliveried==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.ContainerDeliveried);
				dbCommandWrapper.AddInParameter("@POInOpera", DbType.DateTime, shippingStatusReport.POInOpera==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.POInOpera);
				dbCommandWrapper.AddInParameter("@InvValue", DbType.Currency, shippingStatusReport.InvValue==Nulls.NullDecimal?DBNull.Value:(object)shippingStatusReport.InvValue);
				dbCommandWrapper.AddInParameter("@PayDate", DbType.DateTime, shippingStatusReport.PayDate==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.PayDate);
				dbCommandWrapper.AddInParameter("@Remarks", DbType.String, shippingStatusReport.Remarks);
				dbCommandWrapper.AddInParameter("@SInvoiced", DbType.Int32, shippingStatusReport.SInvoiced==Nulls.NullInteger?DBNull.Value:(object)shippingStatusReport.SInvoiced);
				dbCommandWrapper.AddInParameter("@SuppOrderNo", DbType.AnsiString, shippingStatusReport.SuppOrderNo);
				dbCommandWrapper.AddInParameter("@OnHold", DbType.Boolean, shippingStatusReport.OnHold);
				dbCommandWrapper.AddInParameter("@OfficeID", DbType.Int32, shippingStatusReport.OfficeID);
				dbCommandWrapper.AddInParameter("@CustPONo", DbType.AnsiString, shippingStatusReport.CustPONo);
				dbCommandWrapper.AddInParameter("@BulkOrderOfficeID", DbType.Int32, shippingStatusReport.BulkOrderOfficeID);
				dbCommandWrapper.AddInParameter("@SInvoicedPd", DbType.DateTime, shippingStatusReport.SInvoicedPd==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.SInvoicedPd);
				dbCommandWrapper.AddInParameter("@ShippingLineID", DbType.Int32, shippingStatusReport.ShippingLineID==Nulls.NullInteger?DBNull.Value:(object)shippingStatusReport.ShippingLineID);
				dbCommandWrapper.AddInParameter("@Catalogue", DbType.String, shippingStatusReport.Catalogue);
				dbCommandWrapper.AddInParameter("@CatalogID", DbType.Int32, shippingStatusReport.CatalogID==Nulls.NullInteger?DBNull.Value:(object)shippingStatusReport.CatalogID);
				dbCommandWrapper.AddInParameter("@PDepValue", DbType.Currency, shippingStatusReport.PDepValue==Nulls.NullDecimal?DBNull.Value:(object)shippingStatusReport.PDepValue);
				//dbCommandWrapper.AddInParameter("@ConPointID", DbType.Int32, shippingStatusReport.ConPointID==Nulls.NullInteger?DBNull.Value:(object)shippingStatusReport.ConPointID);
				dbCommandWrapper.AddInParameter("@SalesOrder", DbType.Int32, shippingStatusReport.SalesOrder==Nulls.NullInteger?DBNull.Value:(object)shippingStatusReport.SalesOrder);
				dbCommandWrapper.AddInParameter("@SalesInvoiceValue", DbType.Currency, shippingStatusReport.SalesInvoiceValue==Nulls.NullDecimal?DBNull.Value:(object)shippingStatusReport.SalesInvoiceValue);
				dbCommandWrapper.AddInParameter("@SalesInvoiceDate", DbType.DateTime, shippingStatusReport.SalesInvoiceDate==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.SalesInvoiceDate);
				dbCommandWrapper.AddInParameter("@SalesPaymentDue", DbType.DateTime, shippingStatusReport.SalesPaymentDue==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.SalesPaymentDue);
                dbCommandWrapper.AddInParameter("@TargetETD", DbType.DateTime, shippingStatusReport.TargetETD==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.TargetETD);
				dbCommandWrapper.AddInParameter("@IsArchived", DbType.Boolean, shippingStatusReport.IsArchived);
				dbCommandWrapper.AddInParameter("@ResponsibleID", DbType.Int32, shippingStatusReport.ResponsibleID==Nulls.NullInteger?DBNull.Value:(object)shippingStatusReport.ResponsibleID);
				dbCommandWrapper.AddInParameter("@FactoryETDEnteredDate", DbType.DateTime, shippingStatusReport.FactoryETDEnteredDate==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.FactoryETDEnteredDate);
				dbCommandWrapper.AddInParameter("@FeederVessel", DbType.String, shippingStatusReport.FeederVessel);
				dbCommandWrapper.AddInParameter("@IsNomShip", DbType.String, shippingStatusReport.IsNomShip);
				dbCommandWrapper.AddInParameter("@EstActBlank", DbType.String, shippingStatusReport.EstActBlank);
				dbCommandWrapper.AddOutParameter("@ID", DbType.Int32, 0);
                
				DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);
				shippingStatusReport.ID = (int)dbCommandWrapper.GetParameterValue("@ID");
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessCreateShippingStatusReportException, ex);
			}
		}

		public override void UpdateShippingStatusReport(ShippingStatusReport shippingStatusReport)
		{
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportUpdate");            
                
				dbCommandWrapper.AddInParameter("@ID", DbType.Int32, shippingStatusReport.ID);
				dbCommandWrapper.AddInParameter("@REQ", DbType.Int32, shippingStatusReport.REQ);
				dbCommandWrapper.AddInParameter("@OrderDate", DbType.DateTime, shippingStatusReport.OrderDate==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.OrderDate);
				dbCommandWrapper.AddInParameter("@ContainerSize", DbType.AnsiString, shippingStatusReport.ContainerSize);
				dbCommandWrapper.AddInParameter("@LP", DbType.AnsiStringFixedLength, shippingStatusReport.LP);
				dbCommandWrapper.AddInParameter("@PONo_Office", DbType.AnsiStringFixedLength, shippingStatusReport.PONo_Office);
				dbCommandWrapper.AddInParameter("@PONo_Type", DbType.AnsiStringFixedLength, shippingStatusReport.PONo_Type);
				dbCommandWrapper.AddInParameter("@PONo_Supplier", DbType.AnsiString, shippingStatusReport.PONo_Supplier);
				dbCommandWrapper.AddInParameter("@PONo_Number", DbType.Int32, shippingStatusReport.PONo_Number);
				dbCommandWrapper.AddInParameter("@IsPrimary", DbType.AnsiStringFixedLength, shippingStatusReport.IsPrimary);
				dbCommandWrapper.AddInParameter("@PONo_Consolidation", DbType.Int32, shippingStatusReport.PONo_Consolidation==Nulls.NullInteger?DBNull.Value:(object)shippingStatusReport.PONo_Consolidation);
				dbCommandWrapper.AddInParameter("@AccNo", DbType.AnsiString, shippingStatusReport.AccNo);
				dbCommandWrapper.AddInParameter("@CustomerOrLocation", DbType.String, shippingStatusReport.CustomerOrLocation);
				dbCommandWrapper.AddInParameter("@FinalDestination", DbType.String, shippingStatusReport.FinalDestination);
				dbCommandWrapper.AddInParameter("@Description", DbType.String, shippingStatusReport.Description);
				dbCommandWrapper.AddInParameter("@FactoryETD", DbType.DateTime, shippingStatusReport.FactoryETD==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.FactoryETD);
				dbCommandWrapper.AddInParameter("@ShippingDate", DbType.DateTime, shippingStatusReport.ShippingDate==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.ShippingDate);
				dbCommandWrapper.AddInParameter("@ShippingLine", DbType.AnsiString, shippingStatusReport.ShippingLine);
				dbCommandWrapper.AddInParameter("@Vessel", DbType.String, shippingStatusReport.Vessel);
				dbCommandWrapper.AddInParameter("@ContainerNo", DbType.AnsiString, shippingStatusReport.ContainerNo);
				dbCommandWrapper.AddInParameter("@ETA", DbType.DateTime, shippingStatusReport.ETA==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.ETA);
				dbCommandWrapper.AddInParameter("@Week", DbType.Int32, shippingStatusReport.Week);
				dbCommandWrapper.AddInParameter("@ArrivalPort", DbType.String, shippingStatusReport.ArrivalPort);
				dbCommandWrapper.AddInParameter("@PI", DbType.AnsiString, shippingStatusReport.PI);
				dbCommandWrapper.AddInParameter("@Payment", DbType.AnsiStringFixedLength, shippingStatusReport.Payment);
				dbCommandWrapper.AddInParameter("@DocReceived", DbType.Boolean, shippingStatusReport.DocReceived);
				dbCommandWrapper.AddInParameter("@CompletedDate", DbType.DateTime, shippingStatusReport.CompletedDate==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.CompletedDate);
				dbCommandWrapper.AddInParameter("@BulkType", DbType.AnsiString, shippingStatusReport.BulkType);
				dbCommandWrapper.AddInParameter("@ModifiedDate", DbType.DateTime, shippingStatusReport.ModifiedDate);
				dbCommandWrapper.AddInParameter("@BulkOrderNo", DbType.Int32, shippingStatusReport.BulkOrderNo==Nulls.NullInteger?DBNull.Value:(object)shippingStatusReport.BulkOrderNo);
				dbCommandWrapper.AddInParameter("@PayAuth", DbType.AnsiStringFixedLength, shippingStatusReport.PayAuth);
				dbCommandWrapper.AddInParameter("@ContainerDeliveried", DbType.DateTime, shippingStatusReport.ContainerDeliveried==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.ContainerDeliveried);
				dbCommandWrapper.AddInParameter("@POInOpera", DbType.DateTime, shippingStatusReport.POInOpera==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.POInOpera);
				dbCommandWrapper.AddInParameter("@InvValue", DbType.Currency, shippingStatusReport.InvValue==Nulls.NullDecimal?DBNull.Value:(object)shippingStatusReport.InvValue);
				dbCommandWrapper.AddInParameter("@PayDate", DbType.DateTime, shippingStatusReport.PayDate==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.PayDate);
				dbCommandWrapper.AddInParameter("@Remarks", DbType.String, shippingStatusReport.Remarks);
				dbCommandWrapper.AddInParameter("@SInvoiced", DbType.Int32, shippingStatusReport.SInvoiced==Nulls.NullInteger?DBNull.Value:(object)shippingStatusReport.SInvoiced);
				dbCommandWrapper.AddInParameter("@SuppOrderNo", DbType.AnsiString, shippingStatusReport.SuppOrderNo);
				dbCommandWrapper.AddInParameter("@OnHold", DbType.Boolean, shippingStatusReport.OnHold);
				dbCommandWrapper.AddInParameter("@OfficeID", DbType.Int32, shippingStatusReport.OfficeID);
				dbCommandWrapper.AddInParameter("@CustPONo", DbType.AnsiString, shippingStatusReport.CustPONo);
				dbCommandWrapper.AddInParameter("@BulkOrderOfficeID", DbType.Int32, shippingStatusReport.BulkOrderOfficeID);
				dbCommandWrapper.AddInParameter("@SInvoicedPd", DbType.DateTime, shippingStatusReport.SInvoicedPd==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.SInvoicedPd);
				dbCommandWrapper.AddInParameter("@ShippingLineID", DbType.Int32, shippingStatusReport.ShippingLineID==Nulls.NullInteger?DBNull.Value:(object)shippingStatusReport.ShippingLineID);
				dbCommandWrapper.AddInParameter("@Catalogue", DbType.String, shippingStatusReport.Catalogue);
				dbCommandWrapper.AddInParameter("@CatalogID", DbType.Int32, shippingStatusReport.CatalogID==Nulls.NullInteger?DBNull.Value:(object)shippingStatusReport.CatalogID);
				dbCommandWrapper.AddInParameter("@PDepValue", DbType.Currency, shippingStatusReport.PDepValue==Nulls.NullDecimal?DBNull.Value:(object)shippingStatusReport.PDepValue);
				//dbCommandWrapper.AddInParameter("@ConPointID", DbType.Int32, shippingStatusReport.ConPointID==Nulls.NullInteger?DBNull.Value:(object)shippingStatusReport.ConPointID);
				dbCommandWrapper.AddInParameter("@SalesOrder", DbType.Int32, shippingStatusReport.SalesOrder==Nulls.NullInteger?DBNull.Value:(object)shippingStatusReport.SalesOrder);
				dbCommandWrapper.AddInParameter("@SalesInvoiceValue", DbType.Currency, shippingStatusReport.SalesInvoiceValue==Nulls.NullDecimal?DBNull.Value:(object)shippingStatusReport.SalesInvoiceValue);
				dbCommandWrapper.AddInParameter("@SalesInvoiceDate", DbType.DateTime, shippingStatusReport.SalesInvoiceDate==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.SalesInvoiceDate);
				dbCommandWrapper.AddInParameter("@SalesPaymentDue", DbType.DateTime, shippingStatusReport.SalesPaymentDue==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.SalesPaymentDue);
				dbCommandWrapper.AddInParameter("@TargetETD", DbType.DateTime, shippingStatusReport.TargetETD==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.TargetETD);
				dbCommandWrapper.AddInParameter("@IsArchived", DbType.Boolean, shippingStatusReport.IsArchived);
				dbCommandWrapper.AddInParameter("@ResponsibleID", DbType.Int32, shippingStatusReport.ResponsibleID==Nulls.NullInteger?DBNull.Value:(object)shippingStatusReport.ResponsibleID);
				dbCommandWrapper.AddInParameter("@FactoryETDEnteredDate", DbType.DateTime, shippingStatusReport.FactoryETDEnteredDate==Nulls.NullDate?DBNull.Value:(object)shippingStatusReport.FactoryETDEnteredDate);
				dbCommandWrapper.AddInParameter("@FeederVessel", DbType.String, shippingStatusReport.FeederVessel);
				dbCommandWrapper.AddInParameter("@IsNomShip", DbType.String, shippingStatusReport.IsNomShip);
				dbCommandWrapper.AddInParameter("@EstActBlank", DbType.String, shippingStatusReport.EstActBlank);
				DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessUpdateShippingStatusReportException, ex);
			}
		}
		#endregion		
		
		public ShippingStatusReportCollection GetPaymentNotPaidList() 
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportGetPaymentNotPaidList");
                
				ShippingStatusReportCollection shippingStatusReportCollection = new ShippingStatusReportCollection();
				using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper))
				{
					while (reader.Read())
					{
						ShippingStatusReport shippingStatusReport = CreateShippingStatusReportFromReader(reader);
						shippingStatusReportCollection.Add(shippingStatusReport);
					}
					reader.Close();
				}
				
				return shippingStatusReportCollection;
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetShippingStatusReportsException, ex);
			}
		}

		public ShippingStatusReportCollection GetETAHasPastList() 
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportGetETAHasPastList");
                
				ShippingStatusReportCollection shippingStatusReportCollection = new ShippingStatusReportCollection();
				using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper))
				{
					while (reader.Read())
					{
						ShippingStatusReport shippingStatusReport = CreateShippingStatusReportFromReader(reader);
						shippingStatusReportCollection.Add(shippingStatusReport);
					}
					reader.Close();
				}
				
				return shippingStatusReportCollection;
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetShippingStatusReportsException, ex);
			}
		}

		public ShippingStatusReportCollection GetDocNotReceivedList() 
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportGetDocNotReceivedList");
                
				ShippingStatusReportCollection shippingStatusReportCollection = new ShippingStatusReportCollection();
				using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper))
				{
					while (reader.Read())
					{
						ShippingStatusReport shippingStatusReport = CreateShippingStatusReportFromReader(reader);
						shippingStatusReportCollection.Add(shippingStatusReport);
					}
					reader.Close();
				}
				
				return shippingStatusReportCollection;
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetShippingStatusReportsException, ex);
			}
		}

		public IDataReader GetMissingDataReport(int officeID, int type) 
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportGetMissingDataReport");
				dbCommandWrapper.AddInParameter("@OfficeID", DbType.Int32, officeID);
				dbCommandWrapper.AddInParameter("@type", DbType.Int32, type);
                				
				return DataAccessHelper.ExecuteReader(database, dbCommandWrapper);				
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetShippingStatusReportsException, ex);
			}
		}

		public IDataReader GetLateFactoryETDReport(int officeID, int type) 
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportLateFactoryETDReport");
				dbCommandWrapper.AddInParameter("@OfficeID", DbType.Int32, officeID);
				dbCommandWrapper.AddInParameter("@type", DbType.Int32, type);
                				
				return DataAccessHelper.ExecuteReader(database, dbCommandWrapper);				
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetShippingStatusReportsException, ex);
			}
		}

		public IDataReader GetLateShippedDateReport(int officeID, int type) 
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportGetLateShippedDateReport");
				dbCommandWrapper.AddInParameter("@OfficeID", DbType.Int32, officeID);
				dbCommandWrapper.AddInParameter("@type", DbType.Int32, type);
                				
				return DataAccessHelper.ExecuteReader(database, dbCommandWrapper);				
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetShippingStatusReportsException, ex);
			}
		}

		public IDataReader GetSupplierIncompleteReport(int officeID, int supp) 
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportSupplierIncompleteReport");
				dbCommandWrapper.AddInParameter("@OfficeID", DbType.Int32, officeID);
				dbCommandWrapper.AddInParameter("@Supp", DbType.Int32, supp);
                				
				return DataAccessHelper.ExecuteReader(database, dbCommandWrapper);				
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetShippingStatusReportsException, ex);
			}
		}

		public IDataReader GetContainerDeliveredReport(int officeID, DateTime fromDate, DateTime toDate, int type) 
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportContainerDeliveredReport");
				dbCommandWrapper.AddInParameter("@OfficeID", DbType.Int32, officeID);
				dbCommandWrapper.AddInParameter("@type", DbType.Int32, type);
				dbCommandWrapper.AddInParameter("@fromDate", DbType.DateTime, fromDate);
				dbCommandWrapper.AddInParameter("@toDate", DbType.DateTime, toDate);
                				
				return DataAccessHelper.ExecuteReader(database, dbCommandWrapper);				
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetShippingStatusReportsException, ex);
			}
		}

		public IDataReader GetNonPaymentReport(int officeID, int type) 
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportNonPaymentReport");
				dbCommandWrapper.AddInParameter("@OfficeID", DbType.Int32, officeID);
				dbCommandWrapper.AddInParameter("@type", DbType.Int32, type);
			
				return DataAccessHelper.ExecuteReader(database, dbCommandWrapper);				
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetShippingStatusReportsException, ex);
			}
		}

		public IDataReader GetNonPaymentLess7Report(int officeID, int type) 
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportNonPaymentLess7Report");
				dbCommandWrapper.AddInParameter("@OfficeID", DbType.Int32, officeID);
				dbCommandWrapper.AddInParameter("@type", DbType.Int32, type);
			
				return DataAccessHelper.ExecuteReader(database, dbCommandWrapper);				
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetShippingStatusReportsException, ex);
			}
		}

		public IDataReader GetOrderNotPlacedWithFactoryReport(int officeID, int type, int conpointID, string supplierCode) 
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportOrderNotPlaceWithFactoryReport");
				dbCommandWrapper.AddInParameter("@OfficeID", DbType.Int32, officeID);
				dbCommandWrapper.AddInParameter("@type", DbType.Int32, type);
				dbCommandWrapper.AddInParameter("@conpointID", DbType.Int32, conpointID);
				dbCommandWrapper.AddInParameter("@supplierCode", DbType.AnsiString, supplierCode);
			
				return DataAccessHelper.ExecuteReader(database, dbCommandWrapper);				
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetShippingStatusReportsException, ex);
			}
		}

		public IDataReader GetSupplierList(int officeID) 
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportGetSupplierList");
				dbCommandWrapper.AddInParameter("@OfficeID", DbType.Int32, officeID);				
                				
				return DataAccessHelper.ExecuteReader(database, dbCommandWrapper);				
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetShippingStatusReportsException, ex);
			}
		}

		public  int GetTotalSalesOrderFound(int officeID, int poType, int salesOrder) 
		{
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportGetTotalSalesOrder");
				dbCommandWrapper.AddInParameter("@OfficeID", DbType.Int32, officeID);							
				dbCommandWrapper.AddInParameter("@poType", DbType.Int32, poType);
				dbCommandWrapper.AddInParameter("@salesOrder", DbType.Int32, salesOrder);
                				
				return (int)DataAccessHelper.ExecuteScalar(database, dbCommandWrapper);				
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetShippingStatusReportsException, ex);
			}
		}


		public IDataReader GetShippingStatusReports(int officeID, int sortType, int poType, int salesOrder, int page, int pageSize, out int totalRecords) 
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportGetListBySortType");
				dbCommandWrapper.AddInParameter("@OfficeID", DbType.Int32, officeID);
				dbCommandWrapper.AddInParameter("@sortType", DbType.Int32, sortType);				
				dbCommandWrapper.AddInParameter("@poType", DbType.Int32, poType);
				dbCommandWrapper.AddInParameter("@salesOrder", DbType.Int32, salesOrder);
				dbCommandWrapper.AddInParameter("@Page", DbType.Int32, page);
				dbCommandWrapper.AddInParameter("@PageSize", DbType.Int32, pageSize);
				dbCommandWrapper.AddOutParameter("@TotalRecords", DbType.Int32, 4);                          								

				IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper);		
				//totalRecords = (int)dbCommandWrapper.GetParameterValue("@TotalRecords");
				totalRecords = 0;
				return reader;
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetShippingStatusReportsException, ex);
			}
		}

		public IDataReader GetShippingStatusReports(int officeID, int supplierCode, int orderNumber, DateTime orderDate, bool incompleted) 
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportGetListByParam");
				dbCommandWrapper.AddInParameter("@OfficeID", DbType.Int32, officeID);
				dbCommandWrapper.AddInParameter("@supplierCode", DbType.Int32, supplierCode);				
				dbCommandWrapper.AddInParameter("@orderNumber", DbType.Int32, orderNumber);
				dbCommandWrapper.AddInParameter("@orderDate", DbType.DateTime, orderDate);				
				dbCommandWrapper.AddInParameter("@incompleted", DbType.Boolean, incompleted);				

				IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper);						
				return reader;
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetShippingStatusReportsException, ex);
			}
		}

		public ShippingStatusReportCollection GetShippingStatusReports(int req, int officeID) 
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportGetListByReq");
				dbCommandWrapper.AddInParameter("@req", DbType.Int32, req);
				dbCommandWrapper.AddInParameter("@officeID", DbType.Int32, officeID);
                
				ShippingStatusReportCollection shippingStatusReportCollection = new ShippingStatusReportCollection();
				using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper))
				{
					while (reader.Read())
					{
						ShippingStatusReport shippingStatusReport = CreateShippingStatusReportFromReader(reader);
						shippingStatusReportCollection.Add(shippingStatusReport);
					}
					reader.Close();
				}
				
				return shippingStatusReportCollection;
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetShippingStatusReportsException, ex);
			}
		}

		public int GetNextReqNumber(int officeID) 
		{
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportGetNextReq");
				dbCommandWrapper.AddInParameter("@OfficeID", DbType.Int32, officeID);
				
				return Convert.ToInt32(DataAccessHelper.ExecuteScalar(database, dbCommandWrapper));				
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetShippingStatusReportsException, ex);
			}
		}

		public int GetNextSalesOrderNumber() 
		{
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportGetNextSalesOrder");
				
				return Convert.ToInt32(DataAccessHelper.ExecuteScalar(database, dbCommandWrapper));				
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetShippingStatusReportsException, ex);
			}
		}

		public int CheckUniqueSalesOrder(int salesOrder, int req) 
		{
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spShippingStatusReportCheckUniqueSalesOrder");
				
				dbCommandWrapper.AddInParameter("@req", DbType.Int32, req);
				dbCommandWrapper.AddInParameter("@SalesOrder", DbType.Int32, salesOrder);

				return Convert.ToInt32(DataAccessHelper.ExecuteScalar(database, dbCommandWrapper));				
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetShippingStatusReportsException, ex);
			}
		}
	}
}

